package com.ics.atable.chat.service.impl;

import com.ics.atable.chat.model.dto.ItemImportDTO;
import com.ics.atable.chat.service.ExcelService;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

/**
 * Excel文件处理服务实现类
 *
 * @author Chawu
 */
@Slf4j
@Service
public class ExcelServiceImpl implements ExcelService {

    @Override
    public List<ItemImportDTO> parseExcelFile(MultipartFile file) throws Exception {
        List<ItemImportDTO> itemList = new ArrayList<>();
        
        log.info("开始解析Excel文件: {}, 大小: {} bytes", file.getOriginalFilename(), file.getSize());
        
        try {
            // 检查文件是否为空
            if (file.isEmpty()) {
                throw new IllegalArgumentException("上传的文件为空");
            }
            
            // 检查文件大小
            if (file.getSize() > 10 * 1024 * 1024) {
                throw new IllegalArgumentException("文件大小超过10MB限制");
            }
            
            log.info("开始创建Workbook对象...");
            
            // 创建Workbook对象
            Workbook workbook = null;
            try {
                workbook = new XSSFWorkbook(file.getInputStream());
                log.info("Workbook对象创建成功");
            } catch (Exception e) {
                log.error("创建Workbook对象失败", e);
                throw new Exception("无法解析Excel文件，请确保文件格式正确: " + e.getMessage());
            }
            
            try {
                // 获取第一个工作表
                Sheet sheet = workbook.getSheetAt(0);
                log.info("获取工作表成功，总行数: {}", sheet.getLastRowNum() + 1);
                
                // 获取表头行
                Row headerRow = sheet.getRow(0);
                if (headerRow == null) {
                    throw new IllegalArgumentException("Excel文件没有表头行");
                }
                
                log.info("开始验证表头...");
                // 验证表头
                validateHeaders(headerRow);
                log.info("表头验证通过");
                
                // 从第二行开始读取数据
                int validRowCount = 0;
                for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                    Row row = sheet.getRow(i);
                    if (row == null) {
                        log.debug("第{}行为空，跳过", i + 1);
                        continue;
                    }
                    
                    ItemImportDTO item = parseRowToItem(row);
                    if (item != null) {
                        itemList.add(item);
                        validRowCount++;
                    }
                }
                
                log.info("成功解析Excel文件，共读取{}条有效数据", validRowCount);
                
            } finally {
                // 确保Workbook被正确关闭
                try {
                    workbook.close();
                    log.info("Workbook已关闭");
                } catch (IOException e) {
                    log.warn("关闭Workbook时出现异常", e);
                }
            }
            
        } catch (IllegalArgumentException e) {
            log.error("参数验证失败: {}", e.getMessage());
            throw e;
        } catch (IOException e) {
            log.error("读取Excel文件失败", e);
            throw new Exception("读取Excel文件失败: " + e.getMessage());
        } catch (Exception e) {
            log.error("解析Excel文件时发生未知错误", e);
            throw new Exception("解析Excel文件失败: " + e.getMessage());
        }
        
        return itemList;
    }
    
    /**
     * 验证表头是否正确
     */
    private void validateHeaders(Row headerRow) {
        String[] expectedHeaders = {"item_uuid", "item_full_name", "item_name",  "item_father_name", "item_grandfather_name", "metadata"};
        
        log.info("开始验证表头，期望的表头: {}", String.join(", ", expectedHeaders));
        
        for (int i = 0; i < expectedHeaders.length; i++) {
            Cell cell = headerRow.getCell(i);
            String actualValue = cell != null ? getCellValueAsString(cell) : "空";
            log.info("第{}列表头: 期望='{}', 实际='{}'", i + 1, expectedHeaders[i], actualValue);
            
            if (cell == null || !expectedHeaders[i].equals(actualValue)) {
                String errorMsg = String.format("表头格式不正确，第%d列期望: '%s'，实际: '%s'", 
                    i + 1, expectedHeaders[i], actualValue);
                log.error(errorMsg);
                throw new IllegalArgumentException(errorMsg);
            }
        }
        
        log.info("表头验证完成，所有列都符合要求");
    }
    
    /**
     * 将行数据解析为ItemImportDTO对象
     */
    private ItemImportDTO parseRowToItem(Row row) {
        try {
            ItemImportDTO item = new ItemImportDTO();
            
            // 获取item_uuid
            Cell uuidCell = row.getCell(0);
            item.setItemUuid(getCellValueAsString(uuidCell));

            // 获取item_full_name
            Cell fullNameCell = row.getCell(1);
            item.setItemFullName(getCellValueAsString(fullNameCell));
                        
            // 获取item_name
            Cell nameCell = row.getCell(2);
            item.setItemName(getCellValueAsString(nameCell));
            
            // 获取item_father_name
            Cell fatherNameCell = row.getCell(3);
            item.setItemFatherName(getCellValueAsString(fatherNameCell));
            
            // 获取item_grandfather_name
            Cell grandfatherNameCell = row.getCell(4);
            item.setItemGrandfatherName(getCellValueAsString(grandfatherNameCell));
            
            // 获取metadata
            Cell metadataCell = row.getCell(5);
            item.setMetadata(getCellValueAsString(metadataCell));
            
            // 如果所有字段都为空，则跳过该行
            if (isEmpty(item.getItemUuid()) && isEmpty(item.getItemName()) && isEmpty(item.getItemFullName()) && 
                isEmpty(item.getItemFatherName()) && isEmpty(item.getItemGrandfatherName()) && isEmpty(item.getMetadata())) {
                return null;
            }
            
            return item;
            
        } catch (Exception e) {
            log.warn("解析第{}行数据失败: {}", row.getRowNum() + 1, e.getMessage());
            return null;
        }
    }
    
    /**
     * 获取单元格的字符串值
     */
    private String getCellValueAsString(Cell cell) {
        if (cell == null) {
            return "";
        }
        
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue().trim();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    // 处理数字类型，避免科学计数法
                    double numericValue = cell.getNumericCellValue();
                    if (numericValue == (long) numericValue) {
                        return String.valueOf((long) numericValue);
                    } else {
                        return String.valueOf(numericValue);
                    }
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";
        }
    }
    
    /**
     * 判断字符串是否为空
     */
    private boolean isEmpty(String str) {
        return str == null || str.trim().isEmpty();
    }
}
